from sqlalchemy import func, insert, select, update
from forward.api.admin.schemas.salesman import (
    SalesmanAddInSchema,
    SalesmanDeleteInSchema,
    SalesmanDetailInSchema,
    SalesmanEditInSchema,
    SalesmanListInSchema,
    SalesmanOutSchema,
    SalesmanStatusInSchema,
)
from forward.api.admin.schemas.system import SystemAuthAdminOutSchema, SystemAuthDeptOutSchema, SystemAuthPostOutSchema, SystemAuthRoleSchema
from forward.extend.pydantic.result_api import ResultAPI
from forward.model.tables.salesman import SalesmanModel
from forward.dependencies.database import db
from forward.model.tables.system import SystemAuthAdminModel, SystemAuthDeptModel, SystemAuthPostModel, SystemAuthRoleModel
from forward.utils.urls import UrlUtil


class SalesmanService:
    @classmethod
    def instance(cls):
        return SalesmanService()
    async def lists(self, list_in: SalesmanListInSchema):
        dept_id = 13
        where = []
        where.append(SystemAuthAdminModel.is_delete == 0)
        where.append(SystemAuthAdminModel.dept_ids.like(f"%{str(dept_id)}%"))
        if list_in.name:
            where.append(SystemAuthAdminModel.nickname.like(f"%{list_in.name}%"))

        statement = (
            select(
                SystemAuthAdminModel,
                SystemAuthAdminModel.role_ids.label("role"),
                SystemAuthAdminModel.dept_ids.label("dept"),
                SystemAuthAdminModel.post_ids.label("post"),
            )
            .where(*where)
            .order_by(SystemAuthAdminModel.id.desc())
        )
        records = await db.fetch_all(statement)
        admins = [
            SystemAuthAdminOutSchema.model_validate(record, from_attributes=True)
            for record in records
        ]
        # 处理返回结果
        for obj in admins:
            obj.avatar = await UrlUtil.to_absolute_url(obj.avatar)
            if obj.id == 1:
                obj.role = "系统管理员"
            else:
                # 角色
                role_ids = [int(i) for i in (obj.role or "").split(",") if i.isdigit()]
                roles = await db.fetch_all(
                    select(SystemAuthRoleModel).where(
                        SystemAuthRoleModel.id.in_(role_ids)
                    )
                )
                roles = [
                    SystemAuthRoleSchema.model_validate(role, from_attributes=True)
                    for role in roles
                ]
                obj.role = "/".join([i.name for i in roles])
            if not obj.dept:
                obj.dept = ""
            else:
                # 部门
                dept_ids = [int(i) for i in obj.dept.split(",") if i.isdigit()]
                depts_record = await db.fetch_all(
                    select(SystemAuthDeptModel).where(
                        SystemAuthDeptModel.id.in_(dept_ids),
                        SystemAuthDeptModel.is_delete == 0,
                    )
                )
                depts = [
                    SystemAuthDeptOutSchema.model_validate(
                        dept_record, from_attributes=True
                    )
                    for dept_record in depts_record
                ]
                obj.dept = "/".join([i.name for i in depts])
            if not obj.post:
                obj.post = ""
            else:
                # 岗位
                post_ids = [int(i) for i in obj.post.split(",") if i.isdigit()]
                post_record = await db.fetch_all(
                    select(SystemAuthPostModel).where(
                        SystemAuthPostModel.id.in_(post_ids),
                        SystemAuthPostModel.is_delete == 0,
                    )
                )
                posts = [
                    SystemAuthPostOutSchema.model_validate(
                        post_record, from_attributes=True
                    )
                    for post_record in post_record
                ]
                obj.post = "/".join([i.name for i in posts])
            
        return ResultAPI(
            data={
                "count": len(admins),
                "lists": admins,
                "pageNo": list_in.page_no,
                "pageSize": list_in.page_size,
            }
        )

    
        where = []
        if list_in.name:
            where.append(SalesmanModel.name.like(f"%{list_in.name}%"))
        where.append(SalesmanModel.is_deleted == 0)
        statement = (
            select(SalesmanModel)
            .where(*where)
            .order_by(SalesmanModel.id.desc())
            .limit(list_in.page_size)
            .offset((list_in.page_no - 1) * list_in.page_size)
        )
        records = await db.fetch_all(statement)
        statement_count = (
            select(func.count(SalesmanModel.id))
            .where(*where)
            .limit(list_in.page_size)
            .offset((list_in.page_no - 1) * list_in.page_size)
        )
        count = await db.fetch_val(statement_count)
        return ResultAPI(
            data={
                "lists": [
                    SalesmanOutSchema.model_validate(record, from_attributes=True)
                    for record in records
                ],
                "count": count,
            }
        )




        stetement = (
            update(SalesmanModel)
            .where(SalesmanModel.id == status_in.id)
            .values(status=status_in.status)
        )
        await db.execute(stetement)
        return ResultAPI()
